package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryBasedataDao;
import com.boss.salary.entity.SalaryBasedata;
import com.boss.salary.vo.AgencySalaryVO;
import com.boss.sys.utils.SysUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

@Repository
public class SalaryBasedataDaoImpl extends BaseDaoImpl implements ISalaryBasedataDao {

    private static final DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public List<SalaryBasedata> querySalaryBasedataPage(PaginationDTO paginationDTO,String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" id, create_time, update_time, salary_type, salary_code, ")
                .append(" salary_name, base_state, is_standard, is_compute, is_clear, is_accsal_pay, ")
                .append(" is_accsal_withhold, decimal_place, remark,mof_div_code,show_order,is_show ,salary_list_order,is_check ");
        sql.append(" from ")
                .append(TableName.SALARY_BASEDATA);
        sql.append(" where is_deleted = 0 ");
        List<Object> list = new ArrayList<>();
        if (!StringUtils.isEmpty(province)) {
            sql.append(" and mof_div_code = ? ");
            list.add(province);
        }
        sql.append(" order by show_order ");
        String querySql = boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = boxUpParam(list.toArray(), paginationDTO);
        return getJdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(SalaryBasedata.class), params);
    }

    @Override
    public int querySalaryBasedataTotal(String province) {

        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from ").append(TableName.SALARY_BASEDATA);
        sql.append(" where is_deleted = 0 ");
        if (!StringUtils.isEmpty(province)) {
            sql.append(" and mof_div_code = ?");
            return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class,province);
        }else {
            return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class);
        }
    }

    @Override
    public void saveSalaryBasedata(SalaryBasedata salaryBasedata) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.SALARY_BASEDATA);
        sql.append("(id, create_time, update_time, salary_type, salary_code, ")
                .append(" salary_name, base_state, is_standard, is_compute, is_clear, is_accsal_pay, ")
                .append(" is_accsal_withhold, decimal_place, remark, is_deleted, mof_div_code,show_order,is_show,salary_list_order,is_check ) ");
        sql.append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,?,?,?,?,?)");
        // 获取ID
        String id = SysUtils.generateGuid();
        // 获取当前时间
        LocalDateTime dateTime = LocalDateTime.now();
        String now = dateTime.format(dtf);
//        设置参数
        Object[] params = new Object[]{
                id, now, now, salaryBasedata.getSalaryType(), salaryBasedata.getSalaryCode(),
                salaryBasedata.getSalaryName(), salaryBasedata.getBaseState(), salaryBasedata.getIsStandard(), salaryBasedata.getIsCompute(),
                salaryBasedata.getIsClear(), salaryBasedata.getIsAccsalPay(), salaryBasedata.getIsAccsalWithhold(),
                salaryBasedata.getDecimalPlace(), salaryBasedata.getRemark(), salaryBasedata.getMofDivCode(),salaryBasedata.getShowOrder(),salaryBasedata.getIsShow(),salaryBasedata.getSalaryListOrder(),salaryBasedata.getIsCheck()
        };
        super.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateSalaryBasedata(SalaryBasedata salaryBasedata) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.SALARY_BASEDATA);
        sql.append(" set update_time=?, salary_type=?, salary_code=?, salary_name=?, ")
             .append(" base_state=?, is_standard=?, is_compute=?, is_clear=?, is_accsal_pay=?, ")
            .append(" is_accsal_withhold=?, decimal_place=?, remark=?,show_order=?,is_show=?,salary_list_order=?,is_check=? where id=? ");
// 获取当前时间
        LocalDateTime dateTime = LocalDateTime.now();
        String now = dateTime.format(dtf);
        //        设置参数
        Object[] params = new Object[]{
                now, salaryBasedata.getSalaryType(), salaryBasedata.getSalaryCode(), salaryBasedata.getSalaryName(),
                salaryBasedata.getBaseState(), salaryBasedata.getIsStandard(), salaryBasedata.getIsCompute(), salaryBasedata.getIsClear(),
                salaryBasedata.getIsAccsalPay(), salaryBasedata.getIsAccsalWithhold(), salaryBasedata.getDecimalPlace(),
                salaryBasedata.getRemark(),salaryBasedata.getShowOrder(),salaryBasedata.getIsShow(),salaryBasedata.getSalaryListOrder(),salaryBasedata.getIsCheck() ,salaryBasedata.getId()
        };
        super.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteSalaryBasedata(String ids) {
        List<String> idList = Arrays.asList(ids.split(","));
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_BASEDATA);
        sql.append(" set is_deleted = 1 ")
                .append(" where is_deleted = 0 ")
                .append(" and id in (");
        for (int i = 0; i < idList.size(); i++) {
             if(i == idList.size() - 1){
                 sql.append(" ?) ");
             }else{
                 sql.append(" ?, ");
             }
        }
        this.getJdbcTemplate().update(sql.toString(), idList.toArray());
    }

    @Override
    public List<SalaryBasedata> querySalaryBaseDataInfo(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, salary_type, salary_code, salary_name, base_state, is_compute,  ")
                .append("is_clear, is_accsal_pay, is_accsal_withhold, decimal_place, remark, is_deleted,show_order,is_show,is_check from ")
                .append(TableName.SALARY_BASEDATA);
        sql.append(" where is_deleted = '0'").append(" and base_state = '1'");
        if (!StringUtils.isEmpty((String) queryParam.get("province"))) {
            sql.append(" and mof_div_code = ? ");
        }
        sql.append(" order by show_order");
        //请求参数初始化
        if (!StringUtils.isEmpty((String) queryParam.get("province"))) {
            return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class), new Object[]{queryParam.get("province")});
        }else {
            return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class));
        }
    }

    @Override
    public List<SalaryBasedata> queryStandardBasedataInfo(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, salary_type, salary_code, salary_name, base_state, is_compute,  ")
                .append("is_clear, is_accsal_pay, is_accsal_withhold, decimal_place, remark, is_deleted,show_order,is_show,is_Check from ")
                .append(TableName.SALARY_BASEDATA);
        sql.append(" where is_deleted = '0'").append(" and base_state = '1' and is_standard = 1");
        if (!StringUtils.isEmpty((String) queryParam.get("province"))) {
            sql.append(" and mof_div_code = ? ");
        }
        sql.append(" order by show_order");
        //请求参数初始化
        if (!StringUtils.isEmpty((String) queryParam.get("province"))) {
            return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class), new Object[]{queryParam.get("province")});
        }else {
            return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class));
        }
    }

    @Override
    public List<SalaryBasedata> selectAllSalary(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" id, salary_name, is_compute,salary_code ")
                .append(" from ")
                .append(TableName.SALARY_BASEDATA)
                .append(" where ")
                .append(" is_deleted = 0 and base_state = '1' and mof_div_code = ? ")
                .append(" order by show_order");
        return super.getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class), province);
    }

    @Override
    public List<String> selectAllSalaryCode(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" salary_code ")
                .append(" from ")
                .append(TableName.SALARY_BASEDATA)
                .append(" where ")
                .append(" mof_div_code = ? ")
                .append(" order by show_order");
        return super.getjdbcTemplate().queryForList(sb.toString(), String.class,province);
    }

    @Override
    public int getMaxCode(String province){
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" count(*) ")
                .append(" from ")
                .append(TableName.SALARY_BASEDATA);
        if (!StringUtils.isEmpty(province)) {
            sb.append(" where mof_div_code = ?");
            return this.getJdbcTemplate().queryForObject(sb.toString(), Integer.class,province);
        }else {
            return this.getJdbcTemplate().queryForObject(sb.toString(), Integer.class);
        }
    }

    @Override
    public SalaryBasedata selectById(SalaryBasedata salaryBasedata) {
        StringBuilder sb = new StringBuilder();
        sb.append("select id, create_time, update_time, salary_type, salary_code, salary_name, base_state, is_compute,  ")
                .append("is_clear, is_accsal_pay, is_accsal_withhold, decimal_place, remark, is_deleted,show_order,is_show,check from ")
                .append(TableName.SALARY_BASEDATA).append( " where id=? and mof_div_code = ?");
        Object[] params = new Object[]{
                salaryBasedata.getId(),
                salaryBasedata.getMofDivCode()
        };
        List<SalaryBasedata> list = super.getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class),params);
        if(list.size()>0) {
            salaryBasedata = list.get(0);
        }
        return salaryBasedata;
    }

    @Override
    public List<String> getAlladdColum() {
        StringBuilder sb = new StringBuilder();
        sb.append("select salary_code from ")
                .append(TableName.SALARY_BASEDATA)
                .append( " where is_compute=0 and is_accsal_pay=0 and is_accsal_withhold=0 and salary_code<>'s1' and salary_code<>'s2' and salary_code<>'s49' ")
               .append(" and mof_div_code=? ")
                .append(" order by show_order");
        String province="220000000";
        List<String> list = super.getjdbcTemplate().queryForList(sb.toString(), String.class,province);
        return list;
    }

    @Override
    public List<String> getAllsubColum() {
        StringBuilder sb = new StringBuilder();
        sb.append("select salary_code from ")
                .append(TableName.SALARY_BASEDATA)
                .append( " where is_compute=0 and (is_accsal_pay=1 or is_accsal_withhold=1)   and salary_code<>'s1' and salary_code<>'s2' and salary_code<>'s49'  ")
                .append(" and mof_div_code=? ")
                .append(" order by show_order");
        String province="220000000";
        List<String> list = super.getjdbcTemplate().queryForList(sb.toString(), String.class,province);
        return list;
    }

    @Override
    public List<String> selectClearColumn(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select salary_code from ")
                .append(TableName.SALARY_BASEDATA)
                .append(" where  ").append(" mof_div_code = ? and base_state = '1'  ")
                .append(" and is_clear = 1 and is_deleted = 0  ");
        return super.getjdbcTemplate().queryForList(sb.toString(), String.class, province);
    }

    @Override
    public List<SalaryBasedata> selectComputeColumn(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select salary_code from ")
                .append(TableName.SALARY_BASEDATA)
                .append(" where ").append(" mof_div_code = ? and base_state = '1' ")
                .append(" and is_compute = 1 and is_deleted = 0 ").append(" order by show_order");
        return super.getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class), province);
    }

    @Override
    public List<SalaryBasedata> selectAllSalaryAndColumn(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" id, salary_code, is_compute,nvl(decimal_place,0) decimal_place ")
                .append(" from ").append(TableName.SALARY_BASEDATA)
                .append(" where ").append(" mof_div_code = ? ")
               // .append(" and base_state = '1' and is_deleted = 0 ")
                .append(" order by show_order");
        return super.getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class), province);
    }

    @Override
    public List<AgencySalaryVO> selectSalaryItemByProvince(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" salary_code, salary_name ")
                .append(" from ").append(TableName.SALARY_BASEDATA)
                .append(" where ").append(" is_deleted= 0 and base_state = '1' ")
                .append(" and  mof_div_code = ? ")
                .append(" order by ").append(" show_order ");
        return super.getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(AgencySalaryVO.class), province);
    }

    @Override
    public List<SalaryBasedata> getSalaryBaseDataInfoS1S2(String province) {
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, salary_type, salary_code, salary_name, base_state, is_compute,  ")
                .append("is_clear, is_accsal_pay, is_accsal_withhold, decimal_place, remark, is_deleted,show_order,is_show,is_check from ")
                .append(TableName.SALARY_BASEDATA)
                .append(" where is_deleted = '0'").append(" and base_state = '1'")
                .append(" and mof_div_code = ? ")
                .append(" and salary_code in ('s1','s2')")
                .append(" order by show_order");
            return super.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBasedata.class),province);

    }

    @Override
    public List<String> getSalaryCodes(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ").append(" code  ")
                .append(" from ").append(TableName.BASE_DATA)
                .append(" where mof_div_code=? and is_deleted=0 ");
        try {
            return this.getJdbcTemplate().queryForList(sql.toString(),String.class,province);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }



}
